package com.vca.service.service.Impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.StyleSet;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.google.gson.Gson;
import com.google.gson.JsonObject;
import com.vca.common.config.VcaConfig;
import com.vca.common.constants.Constants;
import com.vca.common.exception.VcaException;
import com.vca.common.model.course.Course;
import com.vca.common.model.course.CourseScheduling;
import com.vca.common.model.exhibition.Exhibition;
import com.vca.common.model.exhibition.ExhibitionScheduling;
import com.vca.common.model.export.CourseSchedulingExport;
import com.vca.common.model.order.StoreOrder;
import com.vca.common.model.order.StoreOrderInfo;
import com.vca.common.model.system.SystemGroupData;
import com.vca.common.model.talk.Talk;
import com.vca.common.model.talk.TalkScheduling;
import com.vca.common.model.user.User;
import com.vca.common.model.user.UserCard;
import com.vca.common.request.CardPageRequest;
import com.vca.common.request.StoreDateRangeSqlPram;
import com.vca.common.response.CardPageResponse;
import com.vca.common.utils.*;
import com.vca.common.utils.DateUtil;
import com.vca.common.vo.*;
import com.vca.service.service.*;
import lombok.SneakyThrows;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.DefaultIndexedColorMap;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

import static com.vca.common.utils.ExportUtil.exportExeclByCourser;

@Service
public class ExportServiceImpl implements ExportService {

    @Autowired
    private CourseSchedulingService courseSchedulingService;

    @Autowired
    private CourseService courseService;

    @Autowired
    private InvoiceRecordService invoiceRecordService;

    @Autowired
    private UserService userService;

    @Autowired
    private StoreOrderService orderService;

    @Autowired
    private VcaConfig vcaConfig;

    @Autowired
    private StoreOrderInfoService orderInfoService;

    @Autowired
    private UserCardService cardService;

    @Autowired
    private SystemGroupDataService systemGroupDataService;

    @Autowired
    private TalkSchedulingService talkSchedulingService;

    @Autowired
    private TalkService talkService;

    @Autowired
    private ExhibitionService exhibitionService;

    @Autowired
    private ExhibitionSchedulingService exhibitionSchedulingService;

    @Autowired
    private UserCardService userCardService;

    public static void getWriteOff(Map<String, String> balanceMap, Map<String, String> subtractMap, List<DetailClientVo> vos, Integer maxNum) {
        for (int i = 0; i < vos.size(); i++) {
            DetailClientVo e = vos.get(i);
            //创建剩余金额
            BigDecimal balance = new BigDecimal("0.00");
            if (e.getUserType().equals(1)) {
            } else {
                if (ObjectUtil.isNotEmpty(e.getWriteOffTime())) {
                    Map<Integer, String> schedulingPriceMap = new HashMap<>();
                    List<String> schedulingDates = VcaUtil.stringToArrayStr(e.getSchedulingDate());
                    List<String> schedulingPrices = VcaUtil.stringToArrayStr(e.getSchedulingPrice());
                    for (int j = 0; j < schedulingPrices.size(); j++) {
                        schedulingPriceMap.put(j, schedulingPrices.get(j));
                    }
                    List<String> statusList = VcaUtil.stringToArrayStr(e.getStatus());
                    for (int j = 0; j < maxNum; j++) {
                        if (statusList.size() <= j) {
                        } else if (Integer.parseInt(statusList.get(j)) < 2) {
                            schedulingPriceMap.remove(j);
                        } else {
                            //当被核销后添加到已使用金额中
                            BigDecimal bigDecimal = new BigDecimal(schedulingPriceMap.get(j));
                            balance = balance.add(bigDecimal);
                            int f = 0;
                        }
                    }
                } else {
                    for (int j = 0; j < maxNum; j++) {
                    }
                }
            }
            //支付金额减去已使用金额
            BigDecimal subtract = e.getPayPrice().subtract(balance);
            String s = subtract.compareTo(BigDecimal.ZERO) > 0 ? subtract.toString() : "-";
            String b = balance.compareTo(BigDecimal.ZERO) > 0 ? balance.toString() : "0.00";
            String balanceStr = "";
            String subtractStr = "";
            if (e.getType().equals(4)) {
                if (ObjectUtil.isNotEmpty(e.getCardNo())) {
                    if (ObjectUtil.isEmpty(e.getRefundPrice())) {
                        balanceStr = ObjectUtil.isNotEmpty(balance) ? b : "0.00";
                        subtractStr = ObjectUtil.isNotEmpty(subtract) ? s : "-";
                    } else {
                        balanceStr = "0.00";
                        subtractStr = "-";
                    }

                } else {
                    balanceStr = "";
                    subtractStr = "";
                }
            } else if (ObjectUtil.isNotEmpty(e.getRefundPrice())) {
                balanceStr = "0.00";
                subtractStr = "-";
            } else {
                balanceStr = ObjectUtil.isNotEmpty(balance) ? b : "0.00";
                subtractStr = ObjectUtil.isNotEmpty(subtract) ? s : "-";
            }
            if (ObjectUtil.isEmpty(balanceMap.get(e.getMerOrderId()))) {
                balanceMap.put(e.getMerOrderId(), balanceStr);
            } else {
                if (balanceStr.contains(".")) {
                    String s1 = balanceMap.get(e.getMerOrderId());
                    if (s1.contains(".")) {
                        BigDecimal add = new BigDecimal(s1).add(new BigDecimal(balanceStr));
                        balanceMap.put(e.getMerOrderId(), add.toString());
                    } else {
                        balanceMap.put(e.getMerOrderId(), balanceStr);
                    }
                }
            }

            if (ObjectUtil.isEmpty(subtractMap.get(e.getMerOrderId()))) {
                subtractMap.put(e.getMerOrderId(), subtractStr);
            } else {
                if (subtractStr.contains(".")) {
                    String s1 = subtractMap.get(e.getMerOrderId());
                    if (s1.contains(".")) {
                        BigDecimal add = new BigDecimal(s1).add(new BigDecimal(subtractStr));
                        subtractMap.put(e.getMerOrderId(), add.toString());
                    } else {
                        subtractMap.put(e.getMerOrderId(), subtractStr);
                    }
                }
            }
        }
    }

    /**
     * 根据年月获取当月的天数
     *
     * @param year
     * @param month
     * @return
     */
    public static int getDaysByYearMonth(int year, int month) {

        Calendar a = Calendar.getInstance();

        a.set(Calendar.YEAR, year);

        a.set(Calendar.MONTH, month - 1);

        a.set(Calendar.DATE, 1);

        a.roll(Calendar.DATE, -1);

        int maxDate = a.get(Calendar.DATE);
        return maxDate;
    }

    /**
     * 课程排期导出
     *
     * @return
     */
    @Override
    @SneakyThrows
    public String courserSchedulingExport(String date) {
        //excel文件名
        String fileName = "courseScheduling" + System.currentTimeMillis() + ".xlsx";
        if (StringUtils.isBlank(date)) {
            throw new VcaException("导出日期不能为空");
        }
        int year = Integer.parseInt(date.substring(0, 4));
        int month = Integer.parseInt(date.substring(5, 7));
        //指定日期的天数
        int days = getDaysByYearMonth(year, month);

        List<CourseSchedulingExport> schedulingExportList = courseSchedulingService.getCourserSchedulingExports(date + "-01", date + "-" + days);
        if (CollUtil.isEmpty(schedulingExportList)) {
            throw new VcaException("当前日期没有可导出的数据");
        }


        XSSFWorkbook workbook = exportExeclByCourser(schedulingExportList, year, month);
        // 文件名部分
        ExportUtil.setUpload(vcaConfig.getImagePath(), Constants.UPLOAD_MODEL_PATH_EXCEL, Constants.UPLOAD_TYPE_FILE);

        String newFileName = UploadUtil.getWebPath() + fileName;
        String filePath = UploadUtil.getServerPath();
        // 判断是否存在当前目录，不存在则创建
        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdirs();
        }
        fileName = filePath.concat(fileName);
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(fileName);
            workbook.write(fileOutputStream);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            fileOutputStream.flush();
            fileOutputStream.close();
        }
        return vcaConfig.getServerName() + vcaConfig.getActive() + vcaConfig.getImagePath() + newFileName;
    }

    @Override
    public String detailClient(String dateLimit, String fileName) {
        dateLimitUtilVo dateLimitUtilVo = DateUtil.getDateLimit(dateLimit);
        String startTime = dateLimitUtilVo.getStartTime().substring(0, 10);
        String endTime = DateUtil.addDateMinut(dateLimitUtilVo.getEndTime().substring(0, 10) + " 00:00:00", 24).substring(0, 10);
        dateLimitUtilVo.getEndTime().substring(0, 10);
        List<DetailClientVo> vos = orderService.detailClient(new StoreDateRangeSqlPram(startTime, endTime));
        List<List<String>> content = new ArrayList<>();
        List<String> merOrderIds = new ArrayList<>();
        Map<Integer, String> subscript = new HashMap<>();
        //excel标题
        String title = "detail by client";
        //excel表名
        List<String> headers = new ArrayList<>();
        headers.add("No.");
        headers.add("Client code 客人编号");
        headers.add("Client name 客人姓名");
        headers.add("Product segment 商品分类");
        headers.add("Product name 商品名称");
        headers.add("Receipt number 交易单号");
        headers.add("Merchant order number 商户单号");
        headers.add("Gift card number 礼品卡卡号");
        headers.add("Gift status 赠送状态");
        headers.add("Usage status 使用状态");
        headers.add("Usage time  兑换时间");
        headers.add("Course name 课程名称");
        headers.add("Course period 课程期数");
        headers.add("Payment date 付款日");
        headers.add("Payment total amount 付款总金额");
        headers.add("Payment method 支付方式");
        headers.add("Completed course 已完成期数");
        headers.add("Redeemed amount 已使用金额");
        headers.add("Remain balance 剩余金额");
        headers.add("Fapiao issue date 发票开具日期");
        headers.add("Fapiao type 发票类型");
        headers.add("Fapiao amount 发票金额");
        headers.add("Transaction type 交易类型");
        headers.add("Refund amount 退款金额");
        headers.add("Refund date 退款日期");
        headers.add("Fapiao adjustment amount 发票调整金额");
        headers.add("Fapiao adjustment date 发票调整日期");
        //子表
        Map<String, List<String>> child = new HashMap<>();
        if (ObjectUtil.isEmpty(vos)) {
            return vcaConfig.getServerName() + vcaConfig.getActive() + vcaConfig.getImagePath() + ExportUtil.detailClient(title, headers, content, child, fileName, subscript);
        }
        int maxNum = Collections.max(vos.stream().map(DetailClientVo::getPeriods).collect(Collectors.toList()));
        if (maxNum == 0) {
            maxNum = 1;
        }
        Map<String, String> balanceMap = new HashMap<>();
        Map<String, String> subtractMap = new HashMap<>();
        getWriteOff(balanceMap, subtractMap, vos, maxNum);
        List<String> titles = new ArrayList<>();
        for (int i = 0; i < maxNum; i++) {
            titles.add("Lesson " + (i + 1) + "  第" + NumberToCnUtil.toChineseLower(i + 1) + "期");
        }
        child.put("Completed course 已完成期数", titles);
        for (int i = 0; i < vos.size(); i++) {
            DetailClientVo e = vos.get(i);
            merOrderIds.add(e.getMerOrderId());
            List<String> elementList = new ArrayList<>();
            elementList.add(String.valueOf(i + 1));
            elementList.add(ObjectUtil.isNotEmpty(e.getUid()) ? e.getUid().toString() : "");
            elementList.add(ObjectUtil.isNotEmpty(e.getNickName()) ? e.getNickName() : "");
            elementList.add(ObjectUtil.isNotEmpty(e.getType()) ? VcaUtil.orderTypeToName(e.getType()) : "");
            elementList.add(e.getType().equals(4) ? ObjectUtil.isNotEmpty(e.getName()) ? e.getName().replaceAll(",", "/") : "" : "");
            elementList.add(ObjectUtil.isNotEmpty(e.getOrderNo()) ? e.getOrderNo() : "");
            elementList.add(ObjectUtil.isNotEmpty(e.getUmsMerOrderId()) ? e.getUmsMerOrderId() : "");
            elementList.add(ObjectUtil.isNotEmpty(e.getCardNo()) ? e.getCardNo() : (ObjectUtil.isNotEmpty(e.getCouponNo()) ? e.getCouponNo() : ""));
            elementList.add(ObjectUtil.isNotEmpty(e.getCardUid()) ? e.getUid().equals(e.getCardUid()) ? "未赠送" : "已赠送" : "");
            if (ObjectUtil.isNotEmpty(e.getCardStatus())) {
                if (ObjectUtil.isNotEmpty(e.getPeriods()) && e.getPeriods() > 0) {
                    elementList.add("已使用");
                } else {
                    switch (e.getCardStatus()) {
                        case 0:
                            elementList.add("未使用");
                            break;
                        case 1:
                            elementList.add("已使用");
                            break;
                        case 2:
                            elementList.add("已失效");
                            break;
                        default:
                            elementList.add("");
                            break;
                    }
                }
            } else {
                elementList.add("");
            }
            elementList.add(ObjectUtil.isNotEmpty(e.getUseTime()) ? DateUtil.dateToStr(e.getUseTime(), Constants.DATE_FORMAT) : "");
            if (ObjectUtil.isNotEmpty(e.getCouponName())) {
                elementList.add(ObjectUtil.isNotEmpty(e.getCouponName()) ? VcaUtil.stringToArrayStr(e.getCouponName()).get(0) : "");
                elementList.add(ObjectUtil.isNotEmpty(e.getPeriods()) ? e.getPeriods().toString() : "");
            } else if (!e.getType().equals(4)) {
                elementList.add(ObjectUtil.isNotEmpty(e.getName()) ? VcaUtil.stringToArrayStr(e.getName()).get(0) : "");
                elementList.add(ObjectUtil.isNotEmpty(e.getPeriods()) ? e.getPeriods().toString() : "");
            } else {
                elementList.add("");
                elementList.add("");
            }
            elementList.add(ObjectUtil.isNotEmpty(e.getPayTime()) ? DateUtil.dateToStr(e.getPayTime(), Constants.DATE_FORMAT) : "");
            elementList.add(ObjectUtil.isNotEmpty(e.getTotalPayPrice()) ? e.getTotalPayPrice().toString() : "");
            elementList.add(e.getUserType().equals(1) ? "Presenter" : "Online");
            //创建剩余金额
            BigDecimal balance = new BigDecimal("0.00");
            if (e.getUserType().equals(1)) {
                for (int j = 0; j < maxNum; j++) {
                    elementList.add("");
                    elementList.add("");
                }
            } else {
                if (ObjectUtil.isNotEmpty(e.getWriteOffTime())) {
                    Map<Integer, String> schedulingDateMap = new HashMap<>();
                    Map<Integer, String> schedulingPriceMap = new HashMap<>();
                    List<String> schedulingDates = VcaUtil.stringToArrayStr(e.getWriteOffTime());
                    for (int j = 0; j < schedulingDates.size(); j++) {
                        schedulingDateMap.put(j, schedulingDates.get(j));
                    }
                    List<String> schedulingPrices = VcaUtil.stringToArrayStr(e.getSchedulingPrice());
                    for (int j = 0; j < schedulingPrices.size(); j++) {
                        schedulingPriceMap.put(j, schedulingPrices.get(j));
                    }
                    List<String> statusList = VcaUtil.stringToArrayStr(e.getStatus());
                    for (int j = 0; j < maxNum; j++) {
                        if (statusList.size() <= j) {
                            elementList.add("");
                            elementList.add("");
                        } else if (Integer.parseInt(statusList.get(j)) < 2) {
                            schedulingDateMap.remove(j);
                            schedulingPriceMap.remove(j);
                            elementList.add("");
                            elementList.add("");
                        } else {
                            elementList.add(schedulingDateMap.get(j));
                            elementList.add(schedulingPriceMap.get(j));
                            //当被核销后添加到已使用金额中
                            BigDecimal bigDecimal = new BigDecimal(schedulingPriceMap.get(j));
                            balance = balance.add(bigDecimal);
                            int f = 0;
                        }
                    }
                } else {
                    for (int j = 0; j < maxNum; j++) {
                        elementList.add("");
                        elementList.add("");
                    }
                }
            }
            elementList.add(ObjectUtil.isNotEmpty(balanceMap.get(e.getMerOrderId())) ? balanceMap.get(e.getMerOrderId()) : "0.00");
            elementList.add(ObjectUtil.isNotEmpty(subtractMap.get(e.getMerOrderId())) ? subtractMap.get(e.getMerOrderId()) : "0.00");
            /*//支付金额减去已使用金额
            BigDecimal subtract = e.getPayPrice().subtract(balance);
            String s = subtract.compareTo(BigDecimal.ZERO) > 0 ? subtract.toString() : "-";
            String b = balance.compareTo(BigDecimal.ZERO) > 0 ? balance.toString() : "0.00";
            if (e.getType().equals(4)) {

                if (ObjectUtil.isNotEmpty(e.getCardNo())) {
//                    if (ObjectUtil.isNotEmpty(e.getUseTime())) {
//                        elementList.add(ObjectUtil.isNotEmpty(subtract) ? s : "-");
//                        elementList.add(ObjectUtil.isNotEmpty(balance) ? b : "0.00");
//                    } else {
//                        elementList.add(ObjectUtil.isNotEmpty(balance) ? b : "0.00");
//                        elementList.add(ObjectUtil.isNotEmpty(subtract) ? s : "-");
//                    }
                    if (ObjectUtil.isEmpty(e.getRefundPrice())) {
                        elementList.add(ObjectUtil.isNotEmpty(balance) ? b : "0.00");
                        elementList.add(ObjectUtil.isNotEmpty(subtract) ? s : "-");
                    } else {
                        elementList.add("0.00");
                        elementList.add("-");
                    }

                } else {
                    elementList.add("");
                    elementList.add("");
                }
            } else if (ObjectUtil.isNotEmpty(e.getRefundPrice())) {
                elementList.add("0.00");
                elementList.add("-");
            } else {
                elementList.add(ObjectUtil.isNotEmpty(balance) ? b : "0.00");
                elementList.add(ObjectUtil.isNotEmpty(subtract) ? s : "-");
            }*/
            elementList.add(ObjectUtil.isNotEmpty(e.getInvoiceTime()) ? e.getInvoiceTime() : "");
            elementList.add(ObjectUtil.isNotEmpty(e.getInvoiceTime()) ? "电子增值税普通发票" : "");
            elementList.add(ObjectUtil.isNotEmpty(e.getInvoicePrice()) ? e.getInvoicePrice().toString() : "");
            elementList.add(ObjectUtil.isNotEmpty(e.getRefundPrice()) ? e.getRefundPrice().compareTo(BigDecimal.ZERO) > 0 ? "退款" : "无" : "无");
            elementList.add(ObjectUtil.isNotEmpty(e.getRefundPrice()) ? e.getRefundPrice().compareTo(BigDecimal.ZERO) > 0 ? e.getRefundPrice().toString() : "" : "");
            elementList.add(ObjectUtil.isNotEmpty(e.getRefundTime()) ? DateUtil.dateToStr(e.getRefundTime(), Constants.DATE_FORMAT) : "");
            elementList.add(ObjectUtil.isNotEmpty(e.getCreditNotePrice()) ? e.getCreditNotePrice().toString() : "");
            elementList.add(ObjectUtil.isNotEmpty(e.getCreditNoteTime()) ? e.getCreditNoteTime() : "");
            content.add(elementList);
        }
        for (String merOrderId : merOrderIds) {
            int indexOf = merOrderIds.indexOf(merOrderId);
            if (indexOf != -1) {
                int lastIndexOf = merOrderIds.lastIndexOf(merOrderId);
                if (lastIndexOf != indexOf) {
                    subscript.put(indexOf, indexOf + "," + lastIndexOf);
                }
            }
        }
        ExportUtil.setUpload(vcaConfig.getImagePath(), Constants.UPLOAD_MODEL_PATH_EXCEL, Constants.UPLOAD_TYPE_FILE);
        //创建HSSFWorkbook

        return vcaConfig.getServerName() + vcaConfig.getActive() + vcaConfig.getImagePath() + ExportUtil.detailClient(title, headers, content, child, fileName, subscript);
    }

    @Override
    public String summaryDay(String dateLimit, String fileName) {
        dateLimitUtilVo dateLimitUtilVo = DateUtil.getDateLimit(dateLimit);
        String startTime = dateLimitUtilVo.getStartTime().substring(0, 10);
        String endTime = dateLimitUtilVo.getEndTime().substring(0, 10) + " 23:59:59";
        List<List<String>> content = new ArrayList<>();
        //excel标题
        String title = "summary by day";
        //excel表名
        List<String> headers = new ArrayList<>();
        headers.add("Date");
        headers.add("Payment received by tender");
        headers.add("Payment redeemed during the day");
        headers.add("Payment refund during the day");
        //子表
        Map<String, List<String>> child = new HashMap<>();
        List<String> common = new ArrayList<>();
        common.add("课程/套课/讲座/展览");
        common.add("商品- 书籍/纪念品");
        common.add("商品- 礼品卡");

        List<String> common1 = new ArrayList<>();
        common1.add("课程/套课/讲座/展览");
        common1.add("商品- 书籍/纪念品");
        common1.add("商品- 礼品卡");
        common1.add("套课- 退款差价");
        List<String> received = new ArrayList<>(common);
        received.add("Online wechat");
        child.put("Payment received by tender", received);
        child.put("Payment redeemed during the day", common);
        child.put("Payment refund during the day", common1);
        //received 课程  除了商品订单
        List<StoreOrder> payTime = orderService.list(Wrappers.<StoreOrder>query()
                .select("SUM( pay_price )pay_price,any_value (LEFT ( pay_time, 10 )) pay_time")
                .between("pay_time", startTime, endTime).ne("type", 4)
                .groupBy("date( pay_time )"));
        //received total
        List<StoreOrder> payTotalTime = orderService.list(Wrappers.<StoreOrder>query()
                .select("SUM( pay_price )pay_price,any_value (LEFT ( pay_time, 10 )) pay_time")
                .between("pay_time", startTime, endTime)
                .groupBy("date( pay_time )"));
        //refund 课程 除了商品订单 根据退款时间分组
        List<StoreOrderInfo> refundTime = orderInfoService.list(Wrappers.<StoreOrderInfo>query()
                .select("SUM( refund_price )refund_price,any_value (LEFT ( refund_reason_time, 10 )) refund_reason_time")
                .between("refund_reason_time", startTime, endTime).ne("type", 4)
                .groupBy("date( refund_reason_time )"));
        //redeemed 课程 根据核销时间分组 除了商品订单
        List<StoreOrderInfo> writeOffTime = orderInfoService.list(Wrappers.<StoreOrderInfo>query()
                .select("SUM( pay_price )pay_price,any_value (LEFT ( write_off_time, 10 )) write_off_time")
                .gt("status", 1).between("write_off_time", startTime, endTime)
                .ne("type", 4).groupBy("date( write_off_time )"));
        //refund 商品- 书籍/纪念品 所有商品订单的退款总额
        List<StoreOrderInfo> productRefundTime = orderInfoService.list(Wrappers.<StoreOrderInfo>query()
                .select("refund_price,LEFT ( refund_reason_time, 10 ) refund_reason_time,mer_order_no")
                .between("refund_reason_time", startTime, endTime)
                .eq("type", 4).eq("refund_status", 2));

        //refund 套课- 所有套课退款明细
        List<StoreOrderInfo> packageRefundTime = orderInfoService.list(Wrappers.<StoreOrderInfo>query()
                .select("refund_price,LEFT ( refund_reason_time, 10 ) refund_reason_time,mer_order_no,pay_price")
                .between("refund_reason_time", startTime, endTime)
                .eq("type", 1).eq("refund_status", 2));

        List<StoreOrderInfo> packageRefundTime_t = new ArrayList<>();

        packageRefundTime.forEach(storeOrderInfo -> {


            storeOrderInfo.setRefundPrice((ObjectUtil.isNotEmpty(storeOrderInfo.getPayPrice())?storeOrderInfo.getPayPrice():BigDecimal.ZERO).subtract(ObjectUtil.isNotEmpty(storeOrderInfo.getRefundPrice())?storeOrderInfo.getRefundPrice():BigDecimal.ZERO));
            packageRefundTime_t.add(storeOrderInfo);
        });

        Map<Date,BigDecimal> packageRefundTimeMap = packageRefundTime_t.stream().collect(Collectors.toMap(StoreOrderInfo::getRefundReasonTime,StoreOrderInfo::getRefundPrice,BigDecimal::add));


        //redeemed 商品- 书籍/纪念品
        Map<String, UserCard> cardMap = VcaUtil.listToMap(userCardService.list(), "orderId", String.class);
        Map<Date, StoreOrderInfo> productClosingTimeMap = new HashMap<>();
        List<BigDecimal> total = new ArrayList<>();
        List<StoreOrder> orders = orderService.list(new LambdaQueryWrapper<StoreOrder>()
                .between(StoreOrder::getDeliveryTime, startTime, endTime)
                .eq(StoreOrder::getType, 4)
//                .isNotNull(StoreOrder::getClosingTime));
                .isNotNull(StoreOrder::getDeliveryTime));//发货时间
        List<StoreOrderInfo> orderInfos = new ArrayList<>();
        Map<String, StoreOrder> map = new HashMap<>();//orderId->订单
        if (ObjectUtil.isNotEmpty(orders)) {
            orderInfos.addAll(orderInfoService.list(new LambdaQueryWrapper<StoreOrderInfo>()
                    .in(StoreOrderInfo::getOrderNo, orders.stream().map(StoreOrder::getOrderId).collect(Collectors.toList()))));
            map.putAll(VcaUtil.listToMap(orders, "orderId", String.class));
            if (ObjectUtil.isNotEmpty(orderInfos)) {
                orderInfos = orderInfos.stream().filter(p -> {
                    if (ObjectUtil.isNotEmpty(cardMap.get(p.getMerOrderNo()))) {
                        return false;
                    }
                    return true;
                }).collect(Collectors.toList());//保留MerOrderNo空的数据
                if (ObjectUtil.isNotEmpty(orderInfos)) {
                    orderInfos.forEach(p -> {
                        StoreOrder or = map.get(p.getOrderNo());
//                        Date closing = DateUtil.strToDate(DateUtil.dateToStr(or.getClosingTime(), Constants.DATE_FORMAT_DATE), Constants.DATE_FORMAT_DATE);
                        Date closing = DateUtil.strToDate(DateUtil.dateToStr(or.getDeliveryTime(), Constants.DATE_FORMAT_DATE), Constants.DATE_FORMAT_DATE);
                        StoreOrderInfo order = productClosingTimeMap.get(closing);
                        if (ObjectUtil.isNotEmpty(order)) {
                            order.setPayPrice(order.getPayPrice().add(p.getPayPrice()));
                            StoreOrderInfo o = new StoreOrderInfo();
                            BeanUtils.copyProperties(order, o);
                            productClosingTimeMap.put(closing, o);
                        } else {
                            productClosingTimeMap.put(closing, p);
                        }
                        total.add(p.getPayPrice());
                    });
                }
            }
        }
        //received 商品- 书籍/纪念品
        List<StoreOrder> productPayTime =
                orderService.productPay(startTime, endTime)
                        .stream().filter(p -> ObjectUtil.isNotEmpty(p.getPayTime()))
                        .collect(Collectors.toList());
        Map<Date, StoreOrder> productPayTimeMap = new HashMap<>();
        productPayTime = productPayTime.stream().filter(p -> {
            if (ObjectUtil.isNotEmpty(cardMap.get(p.getOrderId()))) {
                return false;
            }
            return true;
        }).collect(Collectors.toList());
        if (ObjectUtil.isNotEmpty(productPayTime)) {
            productPayTime.forEach(p -> {
                StoreOrder order = productPayTimeMap.get(p.getPayTime());
                if (ObjectUtil.isNotEmpty(order)) {
                    StoreOrder o = new StoreOrder();
                    BeanUtils.copyProperties(order, o);
                    o.setPayPrice(o.getPayPrice().add(p.getPayPrice()));
                    productPayTimeMap.put(p.getPayTime(), o);
                } else {
                    productPayTimeMap.put(p.getPayTime(), p);
                }
            });
        }

        List<StoreOrderInfo> cardRefundList = orderInfoService.cardRefundList(startTime, endTime);
        cardRefundList.addAll(orderInfoService.couponRefundList(startTime, endTime));
        Map<Date, StoreOrderInfo> cardRefundMap = new HashMap<>();
        if (ObjectUtil.isNotEmpty(cardRefundList)) {
            cardRefundList.forEach(p -> {
                StoreOrderInfo order = cardRefundMap.get(p.getRefundReasonTime());
                if (ObjectUtil.isNotEmpty(order)) {
                    if (ObjectUtil.isNotEmpty(p.getPayPrice())) {
                        StoreOrderInfo o = new StoreOrderInfo();
                        BeanUtils.copyProperties(order, o);
                        o.setPayPrice(o.getPayPrice().add(p.getPayPrice()));
                        cardRefundMap.put(p.getRefundReasonTime(), o);
                    }
                } else {
                    cardRefundMap.put(p.getRefundReasonTime(), p);
                }
            });
        }
        Map<Date, StoreOrderInfo> productRefundTimeMap = new HashMap<>();
        productRefundTime = productRefundTime.stream().filter(p -> {
            if (ObjectUtil.isNotEmpty(cardMap.get(p.getMerOrderNo()))) {
                return false;
            }
            return true;
        }).collect(Collectors.toList());
        if (ObjectUtil.isNotEmpty(productRefundTime)) {
            productRefundTime.forEach(p -> {
                StoreOrderInfo order = productRefundTimeMap.get(p.getRefundReasonTime());
                if (ObjectUtil.isNotEmpty(order)) {
                    StoreOrderInfo o = new StoreOrderInfo();
                    BeanUtils.copyProperties(order, o);
                    o.setRefundPrice(o.getRefundPrice().add(p.getRefundPrice()));
                    productRefundTimeMap.put(p.getRefundReasonTime(), o);
                } else {
                    productRefundTimeMap.put(p.getRefundReasonTime(), p);
                }
            });
        }
        List<StoreOrderInfo> infos = orderInfoService.list(
                Wrappers.<StoreOrderInfo>lambdaQuery()
                        .between(StoreOrderInfo::getWriteOffTime, startTime, endTime));
        List<StoreOrder> writeOffCoupon = new ArrayList<>();
        if (ObjectUtil.isNotEmpty(infos)) {
            List<StoreOrder> writeOff = new ArrayList<>();
            List<String> orderNos = infos.stream().map(StoreOrderInfo::getOrderNo).collect(Collectors.toList());
            Map<String, List<StoreOrderInfo>> orderInfoMap = infos.stream().collect(Collectors.groupingBy(StoreOrderInfo::getOrderNo));
            List<StoreOrder> storeOrders = orderService.list(
                    Wrappers.<StoreOrder>lambdaQuery()
                            .in(StoreOrder::getOrderId, orderNos)
                            .gt(StoreOrder::getCouponId, 0));
            if (ObjectUtil.isNotEmpty(storeOrders)) {
                Map<Integer, StoreOrder> orderMap = VcaUtil.listToMap(storeOrders, "couponId", Integer.class);
                List<Integer> couponIds = storeOrders.stream().map(StoreOrder::getCouponId).collect(Collectors.toList());
                List<UserCard> userCards = userCardService.list(
                        Wrappers.<UserCard>lambdaQuery()
                                .in(UserCard::getId, couponIds));
                if (ObjectUtil.isNotEmpty(userCards)) {
                    Map<Integer, UserCard> listToMap = VcaUtil.listToMap(userCards, "id", Integer.class);
                    couponIds.forEach(c -> {
                        StoreOrder storeOrder = new StoreOrder();
                        UserCard userCard = listToMap.get(c);
                        storeOrder.setCouponId(c);
                        storeOrder.setOrderId(userCard.getOrderId());
                        StoreOrder order = orderMap.get(c);
                        List<StoreOrderInfo> infoList = orderInfoMap.get(order.getOrderId());
                        Date writeOffDate = DateUtil.strToDate(DateUtil.dateToStr(infoList.get(0).getWriteOffTime(), Constants.DATE_FORMAT_DATE), Constants.DATE_FORMAT_DATE);
                        storeOrder.setClosingTime(writeOffDate);
                        writeOff.add(storeOrder);
                    });
                    List<String> collect = userCards.stream()
                            .map(UserCard::getOrderId)
                            .filter(ObjectUtil::isNotEmpty)
                            .distinct().collect(Collectors.toList());

                    if(collect.size()>0){
                        List<StoreOrderInfo> storeOrderList = orderInfoService
                                .list(Wrappers.<StoreOrderInfo>lambdaQuery()
                                        .in(StoreOrderInfo::getMerOrderNo, collect)
                                        .gt(StoreOrderInfo::getPayNum, 1));
                        if (ObjectUtil.isNotEmpty(storeOrderList)) {
                            Map<String, StoreOrderInfo> merOrderNo = VcaUtil.listToMap(storeOrderList, "merOrderNo", String.class);
                            writeOff.forEach(w -> {
                                StoreOrderInfo storeOrderInfo = merOrderNo.get(w.getOrderId());
                                if (ObjectUtil.isNotEmpty(storeOrderInfo)) {
                                    BigDecimal divide = storeOrderInfo.getPayPrice().divide(BigDecimal.valueOf(storeOrderInfo.getPayNum()), RoundingMode.UNNECESSARY);
                                    w.setPayPrice(divide);
                                }
                            });
                            writeOffCoupon.addAll(writeOff.stream().filter(e -> ObjectUtil.isNotEmpty(e.getPayPrice())).collect(Collectors.toList()));
                        }
                    }
                }
            }
        }

        List<StoreOrder> coupon = orderService.summaryDay(startTime, endTime);

        if (ObjectUtil.isNotEmpty(writeOffCoupon)) {
            coupon.addAll(writeOffCoupon);
        }
        Map<Date, StoreOrder> couponClosingTimeMap = new HashMap<>();
        if (ObjectUtil.isNotEmpty(coupon)) {
            coupon.forEach(c -> {
                StoreOrder order = couponClosingTimeMap.get(c.getClosingTime());
                if (ObjectUtil.isNotEmpty(order)) {
                    StoreOrder o = new StoreOrder();
                    BeanUtils.copyProperties(order, o);
                    o.setPayPrice(o.getPayPrice().add(c.getPayPrice()));
                    couponClosingTimeMap.put(c.getClosingTime(), o);
                } else {
                    couponClosingTimeMap.put(c.getClosingTime(), c);
                }
            });
        }
        List<StoreOrder> cardPayTime = orderService.cardPay(startTime, endTime);
        Map<Date, StoreOrder> cardPayTimeMap = VcaUtil.listToMap(cardPayTime, "payTime", Date.class);
        List<String> dateList = DateUtil.getListDate(dateLimit);
        Map<Date, StoreOrder> payTimeMap = VcaUtil.listToMap(payTime, "payTime", Date.class);
        Map<Date, StoreOrder> payTotalTimeMap = VcaUtil.listToMap(payTotalTime, "payTime", Date.class);
        Map<Date, StoreOrderInfo> refundTimeMap = VcaUtil.listToMap(refundTime, "refundReasonTime", Date.class);
        Map<Date, StoreOrderInfo> writeOffTimeMap = VcaUtil.listToMap(writeOffTime, "writeOffTime", Date.class);

        dateList.forEach(e -> {
            List<String> list = new ArrayList<>();
            list.add(e);
            Date date = DateUtil.strToGMTMinusEightHours(e);
            list.add(ObjectUtil.isNotEmpty(payTimeMap.get(date)) ?
                    (ObjectUtil.isNotEmpty(payTimeMap.get(date).getPayPrice()) ?
                            payTimeMap.get(date).getPayPrice().toString()
                            : "")
                    : "");
            list.add(ObjectUtil.isNotEmpty(productPayTimeMap.get(date)) ?
                    (ObjectUtil.isNotEmpty(productPayTimeMap.get(date).getPayPrice()) ?
                            productPayTimeMap.get(date).getPayPrice().toString()
                            : "")
                    : "");
            list.add(ObjectUtil.isNotEmpty(cardPayTimeMap.get(date)) ?
                    (ObjectUtil.isNotEmpty(cardPayTimeMap.get(date).getPayPrice()) ?
                            cardPayTimeMap.get(date).getPayPrice().toString()
                            : "")
                    : "");
            list.add(ObjectUtil.isNotEmpty(payTotalTimeMap.get(date)) ?
                    (ObjectUtil.isNotEmpty(payTotalTimeMap.get(date).getPayPrice()) ?
                            payTotalTimeMap.get(date).getPayPrice().toString()
                            : "")
                    : "");
            list.add(ObjectUtil.isNotEmpty(writeOffTimeMap.get(date)) ?
                    (ObjectUtil.isNotEmpty(writeOffTimeMap.get(date).getPayPrice()) ?
                            writeOffTimeMap.get(date).getPayPrice().toString()
                            : "")
                    : "");
            list.add(ObjectUtil.isNotEmpty(productClosingTimeMap.get(date)) ?
                    (ObjectUtil.isNotEmpty(productClosingTimeMap.get(date).getPayPrice()) ?
                            productClosingTimeMap.get(date).getPayPrice().toString()
                            : "")
                    : "");
            list.add(ObjectUtil.isNotEmpty(couponClosingTimeMap.get(date)) ?
                    (ObjectUtil.isNotEmpty(couponClosingTimeMap.get(date).getPayPrice()) ?
                            couponClosingTimeMap.get(date).getPayPrice().toString()
                            : "")
                    : "");
            list.add(ObjectUtil.isNotEmpty(refundTimeMap.get(date)) ?
                    (ObjectUtil.isNotEmpty(refundTimeMap.get(date).getRefundPrice()) ?
                            refundTimeMap.get(date).getRefundPrice().toString()
                            : "")
                    : "");
            list.add(ObjectUtil.isNotEmpty(productRefundTimeMap.get(date)) ?
                    (ObjectUtil.isNotEmpty(productRefundTimeMap.get(date).getRefundPrice()) ?
                            productRefundTimeMap.get(date).getRefundPrice().toString()
                            : "")
                    : "");
            list.add(ObjectUtil.isNotEmpty(cardRefundMap.get(date)) ?
                    (ObjectUtil.isNotEmpty(cardRefundMap.get(date).getRefundPrice()) ?
                            cardRefundMap.get(date).getRefundPrice().toString()
                            : "")
                    : "");
            list.add(ObjectUtil.isNotEmpty(packageRefundTimeMap.get(date)) ?
                    (ObjectUtil.isNotEmpty(packageRefundTimeMap.get(date)) ?
                            packageRefundTimeMap.get(date).toString()
                            : "")
                    : "");
            content.add(list);
        });
        List<String> list = new ArrayList<>();
        list.add("Sum");

        list.add(ObjectUtil.isNotEmpty(payTime) ?
                payTime.stream()
                        .map(StoreOrder::getPayPrice)
                        .filter(ObjectUtil::isNotEmpty)
                        .reduce(BigDecimal.ZERO, BigDecimal::add)
                        .toString()
                : "");
        list.add(ObjectUtil.isNotEmpty(productPayTime) ?
                productPayTime.stream()
                        .map(StoreOrder::getPayPrice)
                        .filter(ObjectUtil::isNotEmpty)
                        .reduce(BigDecimal.ZERO, BigDecimal::add)
                        .toString()
                : "");
        list.add(ObjectUtil.isNotEmpty(cardPayTime) ?
                cardPayTime.stream()
                        .map(StoreOrder::getPayPrice)
                        .filter(ObjectUtil::isNotEmpty)
                        .reduce(BigDecimal.ZERO, BigDecimal::add)
                        .toString()
                : "");
        list.add(ObjectUtil.isNotEmpty(payTotalTime) ?
                payTotalTime.stream()
                        .map(StoreOrder::getPayPrice)
                        .filter(ObjectUtil::isNotEmpty)
                        .reduce(BigDecimal.ZERO, BigDecimal::add)
                        .toString()
                : "");
        list.add(ObjectUtil.isNotEmpty(writeOffTime) ?
                writeOffTime.stream()
                        .map(StoreOrderInfo::getPayPrice)
                        .filter(ObjectUtil::isNotEmpty)
                        .reduce(BigDecimal.ZERO, BigDecimal::add)
                        .toString()
                : "");
        list.add(ObjectUtil.isNotEmpty(total) ?
                total.stream()
                        .reduce(BigDecimal.ZERO, BigDecimal::add)
                        .toString()
                : "");
        list.add(ObjectUtil.isNotEmpty(coupon) ?
                coupon.stream()
                        .map(StoreOrder::getPayPrice)
                        .filter(ObjectUtil::isNotEmpty)
                        .reduce(BigDecimal.ZERO, BigDecimal::add)
                        .toString()
                : "");
        list.add(ObjectUtil.isNotEmpty(refundTime) ?
                refundTime.stream()
                        .map(StoreOrderInfo::getRefundPrice)
                        .filter(ObjectUtil::isNotEmpty)
                        .reduce(BigDecimal.ZERO, BigDecimal::add)
                        .toString()
                : "");
        list.add(ObjectUtil.isNotEmpty(productRefundTime) ?
                productRefundTime.stream()
                        .map(StoreOrderInfo::getRefundPrice)
                        .filter(ObjectUtil::isNotEmpty)
                        .reduce(BigDecimal.ZERO, BigDecimal::add)
                        .toString()
                : "");
        list.add(ObjectUtil.isNotEmpty(cardRefundList) ?
                cardRefundList.stream()
                        .map(StoreOrderInfo::getRefundPrice)
                        .filter(ObjectUtil::isNotEmpty)
                        .reduce(BigDecimal.ZERO, BigDecimal::add)
                        .toString()
                : "");
        list.add(ObjectUtil.isNotEmpty(packageRefundTime_t) ?
                packageRefundTime_t.stream()
                        .map(StoreOrderInfo::getRefundPrice)
                        .filter(ObjectUtil::isNotEmpty)
                        .reduce(BigDecimal.ZERO, BigDecimal::add)
                        .toString()
                : "");
        content.add(list);
        ExportUtil.setUpload(vcaConfig.getImagePath(), Constants.UPLOAD_MODEL_PATH_EXCEL, Constants.UPLOAD_TYPE_FILE);
        //创建HSSFWorkbook
        return vcaConfig.getServerName() + vcaConfig.getActive() + vcaConfig.getImagePath() + ExportUtil.summaryDay(title, headers, content, child, fileName);
    }

    @Override
    public String cardStatement(CardPageRequest request, String fileName) {
        //excel标题
        String title = "礼品卡报表";
        //excel表名
        List<String> headers = new ArrayList<>();
        headers.add("No.");
        headers.add("礼品卡卡号");
        headers.add("礼品卡名称");
        headers.add("面值");
        headers.add("礼品卡类型");
        headers.add("创建时间");
        headers.add("过期时间");
        headers.add("礼品卡序列号");
        headers.add("赠送状态");
        headers.add("赠送时间");
        headers.add("备注");
        headers.add("被赠送人");
        headers.add("手机号");
        headers.add("礼品卡状态");
        headers.add("兑换订单号");
        headers.add("订单类型");
        headers.add("订单信息");
        headers.add("预约时间");
        headers.add("下单时间");
        List<List<String>> content = new ArrayList<>();
        ExportUtil.setUpload(vcaConfig.getImagePath(), Constants.UPLOAD_MODEL_PATH_EXCEL, Constants.UPLOAD_TYPE_FILE);
        List<CardPageResponse> responses = cardService.cardPage(request);
        for (int i = 0; i < responses.size(); i++) {
            CardPageResponse r = responses.get(i);
            r.setGiftStatus(ObjectUtil.isNotEmpty(r.getGiftTime()));
            List<String> list = new ArrayList<>();
            list.add(String.valueOf(i + 1));
            list.add(ObjectUtil.isNotEmpty(r.getCardNo()) ? r.getCardNo() : "");
            list.add(ObjectUtil.isNotEmpty(r.getName()) ? r.getName() : "");
            list.add(ObjectUtil.isNotEmpty(r.getFaceValue()) ? r.getFaceValue().toString() : "");
            switch (r.getUserAround()) {
                case 0:
                    list.add("课程");
                    break;
                case 1:
                    list.add("讲座");
                    break;
                case 2:
                    list.add("展览");
                    break;
                default:
                    list.add("");
                    break;
            }
            list.add(ObjectUtil.isNotEmpty(r.getCreateTime()) ? DateUtil.dateToStr(r.getCreateTime()) : "");
            list.add(ObjectUtil.isNotEmpty(r.getEndTime()) ? DateUtil.dateToStr(r.getEndTime()) : "");
            list.add(ObjectUtil.isNotEmpty(r.getCdKey()) ? r.getCdKey() : "");
            list.add(ObjectUtil.isNotEmpty(r.getGiftStatus()) ? r.getGiftStatus().equals(Boolean.TRUE) ? "已赠送" : "未赠送" : "");
            list.add(ObjectUtil.isNotEmpty(r.getGiftTime()) ? DateUtil.dateToStr(r.getGiftTime()) : "");
            list.add(ObjectUtil.isNotEmpty(r.getRemark()) ? r.getRemark() : "");
            list.add(ObjectUtil.isNotEmpty(r.getRecipient()) ? r.getRecipient() : "");
            list.add(ObjectUtil.isNotEmpty(r.getPhone()) ? r.getPhone() : "");
            if (r.getEndTime().compareTo(DateUtil.nowDateTime()) < 0 && ObjectUtil.isEmpty(r.getOrderId())) {
                r.setStatus(2);
            } else {
                if (ObjectUtil.isEmpty(r.getOrderId())) {
                    r.setStatus(0);
                }
                if (ObjectUtil.isEmpty(r.getGiftTime()) || ObjectUtil.isEmpty(r.getRecipient())) {
                    r.setStatus(0);
                }
            }
            switch (r.getStatus()) {
                case 0:
                    list.add("未使用");
                    break;
                case 1:
                    list.add("已使用");
                    break;
                case 2:
                    list.add("已失效");
                    break;
                default:
                    list.add("未知状态");
                    break;
            }
            list.add(ObjectUtil.isNotEmpty(r.getOrderId()) ? r.getOrderId() : "");
            if (ObjectUtil.isNotEmpty(r.getOrderType())) {
                switch (r.getOrderType()) {
                    case 0:
                        list.add("课程");
                        break;
                    case 1:
                        list.add("套课");
                        break;
                    case 2:
                        list.add("讲座");
                        break;
                    case 3:
                        list.add("展览");
                        break;
                    case 4:
                        list.add("商品");
                        break;
                    default:
                        list.add("未知类型");
                        break;
                }
            } else {
                list.add("");
            }
            list.add(ObjectUtil.isNotEmpty(r.getOrderName()) ? r.getOrderName() : "");
            list.add(ObjectUtil.isNotEmpty(r.getSchedulingDate()) ? r.getSchedulingDate() + " " + r.getSchedulingStartTime() : "");
            list.add(ObjectUtil.isNotEmpty(r.getOrderTime()) ? DateUtil.dateToStr(r.getOrderTime()) : "");
            content.add(list);
        }
        BigDecimal faceValue = responses.stream()
                .map(CardPageResponse::getFaceValue)
                .filter(ObjectUtil::isNotEmpty)
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        List<String> list = new ArrayList<>();
        list.add("Sum");
        list.add("");
        list.add("");
        list.add(ObjectUtil.isNotEmpty(faceValue) ? faceValue.toString() : "0.00");
        list.add("");
        list.add("");
        list.add("");
        list.add("");
        list.add("");
        list.add("");
        list.add("");
        list.add("");
        list.add("");
        list.add("");
        list.add("");
        list.add("");
        list.add("");
        list.add("");
        list.add("");
        content.add(list);
        return vcaConfig.getServerName() + vcaConfig.getActive() + vcaConfig.getImagePath() + ExportUtil.exportData(title, headers, content, fileName);
    }

    @Override
    public String exportCdKey(CardPageRequest request, String fileName) {
        //excel标题
        String title = "礼品卡报表";
        //excel表名
        List<String> headers = new ArrayList<>();
        headers.add("No.");
        headers.add("礼品卡卡号");
        headers.add("礼品卡名称");
        headers.add("礼品卡面值");
        headers.add("礼品卡类型");
        headers.add("过期时间");
        headers.add("礼品卡序列号");
        List<List<String>> content = new ArrayList<>();
        ExportUtil.setUpload(vcaConfig.getImagePath(), Constants.UPLOAD_MODEL_PATH_EXCEL, Constants.UPLOAD_TYPE_FILE);
        List<CardPageResponse> responses = cardService.cardPage(request);
        for (int i = 0; i < responses.size(); i++) {
            CardPageResponse r = responses.get(i);
            r.setGiftStatus(ObjectUtil.isNotEmpty(r.getGiftTime()));
            List<String> list = new ArrayList<>();
            list.add(String.valueOf(i + 1));
            list.add(ObjectUtil.isNotEmpty(r.getCardNo()) ? r.getCardNo() : "");
            list.add(ObjectUtil.isNotEmpty(r.getName()) ? r.getName() : "");
            list.add(ObjectUtil.isNotEmpty(r.getFaceValue()) ? r.getFaceValue().toString() : "");
            switch (r.getUserAround()) {
                case 0:
                    list.add("课程");
                    break;
                case 1:
                    list.add("讲座");
                    break;
                case 2:
                    list.add("展览");
                    break;
                default:
                    list.add("未知类型");
                    break;
            }
            list.add(ObjectUtil.isNotEmpty(r.getEndTime()) ? DateUtil.dateToStr(r.getEndTime()) : "");
            list.add(ObjectUtil.isNotEmpty(r.getCdKey()) ? r.getCdKey() : "");
            content.add(list);
        }
        return vcaConfig.getServerName() + vcaConfig.getActive() + vcaConfig.getImagePath() + ExportUtil.exportData(title, headers, content, fileName);
    }

    @Override
    public String exportExcelCourseSchedulingById(String courseSchedulingId) throws IOException {
        /**
         * 查找排期数据
         */
        CourseScheduling courseScheduling = courseSchedulingService.getById(courseSchedulingId);
        if (courseScheduling == null) {
            throw new VcaException("不存在这个课程排期");
        }
        Course course = courseService.getById(courseScheduling.getCourseId());
        if (course == null) {
            throw new VcaException("不存在这个课程");
        }
        String name = course.getName(); //课程名称
        SystemGroupData systemGroupData1 = systemGroupDataService.getById(courseScheduling.getCourseLanguageId());
        if (systemGroupData1 == null) {
            throw new VcaException("不存在这个课程语言");
        }
        Gson gson = new Gson();
        JsonObject value1 = gson.fromJson(systemGroupData1.getValue(), JsonObject.class);
        String value11 = value1.get("fields").getAsJsonArray().get(0).getAsJsonObject().get("value").getAsString(); //排课语言
        SystemGroupData systemGroupData2 = systemGroupDataService.getById(courseScheduling.getPriceId());
        if (systemGroupData2 == null) {
            throw new VcaException("不存在这个课程价格");
        }
        JsonObject value2 = gson.fromJson(systemGroupData2.getValue(), JsonObject.class);
        String asString = value2.get("fields").getAsJsonArray().get(0).getAsJsonObject().get("value").getAsString();
        BigDecimal value22 = new BigDecimal(asString); //排课价格
        //标题
        String[] header = {"课程名称", "课程语言", "日期", "时间", "课程状态", "课程价格", "席位", "外部限量", "外部预约", "已核销人数", "内部限量", "内部预约", "已核销人数", "总出席人数"};
        //标题数据
        ExcelCourseSchedulingById excelCourseById = new ExcelCourseSchedulingById();
        BeanUtils.copyProperties(courseScheduling, excelCourseById);
        boolean status = checkExcelStatus(courseScheduling.getSchedulingDate(), courseScheduling.getSchedulingStartTime(), courseScheduling.getSchedulingEndTime());
        excelCourseById.setStatus(status ? "已结束" : "未开始");
        excelCourseById.setName(name);
        excelCourseById.setCourseLanguage(value11);
        excelCourseById.setPrice(value22);
        excelCourseById.setAllStudentsNumber(excelCourseById.getExternalStudentsNumber() + excelCourseById.getInternalStudentsNumber());
        /**
         * 查找核销或待核销的数据
         */
        String[] header2 = {"学员序号", "预约讲座人员", "预约讲座手机号", "用户昵称", "绑定电话", "绑定邮箱", "订单号", "支付方式", "核销情况", "核销时间"};
        List<ExcelCourseOrderInfo> list = new ArrayList();
        excelCourseById.setList(list);
        LambdaQueryWrapper<StoreOrderInfo> storeOrderInfoLambdaQueryWrapper = new LambdaQueryWrapper<>();
        storeOrderInfoLambdaQueryWrapper.eq(StoreOrderInfo::getSchedulingId, courseSchedulingId);
        //已核销
        LambdaQueryWrapper<StoreOrder> storeOrderLambdaQueryWrapper = new LambdaQueryWrapper<>();
        storeOrderLambdaQueryWrapper.and(wrapper -> wrapper.in(StoreOrder::getStatus, Arrays.asList(2, 3)).in(StoreOrder::getType, Arrays.asList(0, 1, 2, 3)).or(wrapper1 -> wrapper1.eq(StoreOrder::getPaid, false).eq(StoreOrder::getStatus, 0)));
        storeOrderLambdaQueryWrapper.eq(StoreOrder::getIsDel, false);
        List<StoreOrder> storeOrderList = orderService.list(storeOrderLambdaQueryWrapper);
        //已核销主订单
        List<String> storeOrderNoList = null;
        if (CollectionUtil.isNotEmpty(storeOrderList)) {
            storeOrderNoList = storeOrderList.stream().map(StoreOrder::getOrderId).collect(Collectors.toList());
        }
        //待核销
//        storeOrderInfoLambdaQueryWrapper.eq(StoreOrderInfo::getStatus, 1);
//        storeOrderInfoLambdaQueryWrapper.eq(StoreOrderInfo::getRefundStatus, 0);
        final List<String> finalStoreOrderNoList = storeOrderNoList;
        if (CollectionUtil.isNotEmpty(finalStoreOrderNoList)) {
            storeOrderInfoLambdaQueryWrapper.and(wrapper -> wrapper.eq(StoreOrderInfo::getStatus, 1).eq(StoreOrderInfo::getRefundStatus, 0).or().in(CollectionUtil.isNotEmpty(finalStoreOrderNoList), StoreOrderInfo::getOrderNo, finalStoreOrderNoList));
        } else {
            storeOrderInfoLambdaQueryWrapper.eq(StoreOrderInfo::getStatus, 1).eq(StoreOrderInfo::getRefundStatus, 0);
        }
        List<StoreOrderInfo> storeOrderInfos = orderInfoService.list(storeOrderInfoLambdaQueryWrapper);
        if (CollectionUtil.isNotEmpty(storeOrderInfos)) {
            for (int i = 0; i < storeOrderInfos.size(); i++) {
                StoreOrderInfo storeOrderInfo = storeOrderInfos.get(i);
                ExcelCourseOrderInfo excelCourseOrderInfo = new ExcelCourseOrderInfo();
                BeanUtils.copyProperties(storeOrderInfo, excelCourseOrderInfo);
                //购买人
                User buyer = userService.getById(storeOrderInfo.getBuyer());
                if (buyer != null) {
                    excelCourseOrderInfo.setNickname(buyer.getNickname());
                    excelCourseOrderInfo.setPhone(buyer.getPhone());
                    excelCourseOrderInfo.setEmail(buyer.getEmail());
                } else {
//                    throw new VcaException("不存在该购买人：" + storeOrderInfo.getBuyer());
                }
                //主订单
                StoreOrder storeOrder = orderService.getByOrderId(storeOrderInfo.getOrderNo());
                if (storeOrder != null) {
                    //支付渠道
                    if (storeOrder.getUserType() == 1) {
                        excelCourseOrderInfo.setChannel("后台赠送");
                    } else {
                        excelCourseOrderInfo.setChannel("微信支付");
                    }
                    //主订单uid
                    if (StringUtils.isBlank(excelCourseOrderInfo.getNickname()) && storeOrder.getUid() != null) {
                        User user = userService.getById(storeOrder.getUid());
                        if (user != null) {
                            excelCourseOrderInfo.setNickname(user.getNickname());
                            excelCourseOrderInfo.setPhone(user.getPhone());
                            excelCourseOrderInfo.setEmail(user.getEmail());
                        }
                    }
                } else {
//                    throw new VcaException("不存在该主订单：" + storeOrderInfo.getOrderNo());
                }
                //核销状态
                if (storeOrder != null) {
                    if (storeOrder.getPaid()) {
                        if (storeOrderInfo.getWriteOffClerkId() == null) {
                            excelCourseOrderInfo.setWriteOffStatus("待核销");
                        } else {
                            excelCourseOrderInfo.setWriteOffStatus("已核销");
                        }
                    } else {
                        excelCourseOrderInfo.setWriteOffStatus("待支付");
                    }
                }
                //核销时间
                if (storeOrderInfo.getWriteOffTime() != null) {
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm");
                    excelCourseOrderInfo.setWriteOffTime(simpleDateFormat.format(storeOrderInfo.getWriteOffTime()));
                }
                list.add(excelCourseOrderInfo);
            }
        }
        //构建execl格式
        String fileName = "course" + System.currentTimeMillis() + ".xlsx";
        XSSFWorkbook workbook = ExportUtil.exportExcelCourseSchedulingById(header, header2, status, excelCourseById);
        // 文件名部分
        ExportUtil.setUpload(vcaConfig.getImagePath(), Constants.UPLOAD_MODEL_PATH_EXCEL, Constants.UPLOAD_TYPE_FILE);

        String newFileName = UploadUtil.getWebPath() + fileName;
        String filePath = UploadUtil.getServerPath();
        // 判断是否存在当前目录，不存在则创建
        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdirs();
        }
        fileName = filePath.concat(fileName);
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(fileName);
            workbook.write(fileOutputStream);
        } catch (FileNotFoundException | ExceptionInInitializerError e) {
            fileOutputStream = new FileOutputStream(fileName);
            workbook.write(fileOutputStream);
        } finally {
            fileOutputStream.flush();
            fileOutputStream.close();
        }
        return vcaConfig.getServerName() + vcaConfig.getActive() + vcaConfig.getImagePath() + newFileName;
    }

    @Override
    public String exportExcelTalkSchedulingById(String talkSchedulingId) throws IOException {
        /**
         * 查找排期数据
         */
        TalkScheduling talkeScheduling = talkSchedulingService.getById(talkSchedulingId);
        if (talkeScheduling == null) {
            throw new VcaException("不存在这个讲座排期");
        }
        Talk talk = talkService.getById(talkeScheduling.getTalkId());
        if (talk == null) {
            throw new VcaException("不存在这个讲座");
        }
        String name = talk.getName(); //名称
        SystemGroupData systemGroupData1 = systemGroupDataService.getById(talkeScheduling.getCourseLanguageId());
        if (systemGroupData1 == null) {
            throw new VcaException("不存在这个课程语言");
        }
        Gson gson = new Gson();
        JsonObject value1 = gson.fromJson(systemGroupData1.getValue(), JsonObject.class);
        String value11 = value1.get("fields").getAsJsonArray().get(0).getAsJsonObject().get("value").getAsString(); //排课语言
        //标题
        String[] header = {"讲座性质", "讲座名称", "讲座语言", "日期", "时间", "讲座状态", "讲课价格", "限量", "预约人数", "已核销人数（出席人数）"};
        //标题数据
        ExcelTalkSchedulingById excelTalkSchedulingById = new ExcelTalkSchedulingById();
        BeanUtils.copyProperties(talkeScheduling, excelTalkSchedulingById);
        boolean status = checkExcelStatus(talkeScheduling.getSchedulingDate(), talkeScheduling.getSchedulingStartTime(), talkeScheduling.getSchedulingEndTime());
        excelTalkSchedulingById.setStatus(status ? "已结束" : "未开始");
        excelTalkSchedulingById.setTalkType(talk.getTalkType().equals(1) ? "线下" : "线上");
        excelTalkSchedulingById.setName(talk.getName());
        excelTalkSchedulingById.setCourseLanguage(value11);
        /**
         * 查找核销或待核销的数据
         */
        String[] header2 = {"学员序号", "预约讲座人员", "预约讲座手机号", "用户昵称", "绑定电话", "绑定邮箱", "订单号", "支付方式", "核销情况", "核销时间"};
        List<ExcelTalkOrderInfo> list = new ArrayList();
        excelTalkSchedulingById.setList(list);
        LambdaQueryWrapper<StoreOrderInfo> storeOrderInfoLambdaQueryWrapper = new LambdaQueryWrapper<>();
        //已核销
        LambdaQueryWrapper<StoreOrder> storeOrderLambdaQueryWrapper = new LambdaQueryWrapper<>();
        storeOrderInfoLambdaQueryWrapper.eq(StoreOrderInfo::getSchedulingId, talkSchedulingId);
        storeOrderLambdaQueryWrapper.and(wrapper -> wrapper.in(StoreOrder::getStatus, Arrays.asList(2, 3)).in(StoreOrder::getType, Arrays.asList(0, 1, 2, 3)).or(wrapper1 -> wrapper1.eq(StoreOrder::getPaid, false).eq(StoreOrder::getStatus, 0)));
        storeOrderLambdaQueryWrapper.eq(StoreOrder::getIsDel, false);
        List<StoreOrder> storeOrderList = orderService.list(storeOrderLambdaQueryWrapper);
        //已核销主订单
        List<String> storeOrderNoList = null;
        if (CollectionUtil.isNotEmpty(storeOrderList)) {
            storeOrderNoList = storeOrderList.stream().map(StoreOrder::getOrderId).collect(Collectors.toList());
        }
        //待核销
//        storeOrderInfoLambdaQueryWrapper.eq(StoreOrderInfo::getStatus, 1);
//        storeOrderInfoLambdaQueryWrapper.eq(StoreOrderInfo::getRefundStatus, 0);
        final List<String> finalStoreOrderNoList = storeOrderNoList;
        if (CollectionUtil.isNotEmpty(finalStoreOrderNoList)) {
            storeOrderInfoLambdaQueryWrapper.and(wrapper -> wrapper.eq(StoreOrderInfo::getStatus, 1).eq(StoreOrderInfo::getRefundStatus, 0).or().in(CollectionUtil.isNotEmpty(finalStoreOrderNoList), StoreOrderInfo::getOrderNo, finalStoreOrderNoList));
        } else {
            storeOrderInfoLambdaQueryWrapper.eq(StoreOrderInfo::getStatus, 1).eq(StoreOrderInfo::getRefundStatus, 0);
        }
        List<StoreOrderInfo> storeOrderInfos = orderInfoService.list(storeOrderInfoLambdaQueryWrapper);
        if (CollectionUtil.isNotEmpty(storeOrderInfos)) {
            for (int i = 0; i < storeOrderInfos.size(); i++) {
                StoreOrderInfo storeOrderInfo = storeOrderInfos.get(i);
                ExcelTalkOrderInfo excelTalkOrderInfo = new ExcelTalkOrderInfo();
                BeanUtils.copyProperties(storeOrderInfo, excelTalkOrderInfo);
                //购买人
                User buyer = userService.getById(storeOrderInfo.getBuyer());
                if (buyer != null) {
                    excelTalkOrderInfo.setNickname(buyer.getNickname());
                    excelTalkOrderInfo.setPhone(buyer.getPhone());
                    excelTalkOrderInfo.setEmail(buyer.getEmail());
                } else {
//                    throw new VcaException("不存在该购买人：" + storeOrderInfo.getBuyer());
                }
                //主订单
                StoreOrder storeOrder = orderService.getByOrderId(storeOrderInfo.getOrderNo());
                if (storeOrder != null) {
                    //支付渠道
                    if (storeOrder.getUserType() == 1) {
                        excelTalkOrderInfo.setChannel("后台赠送");
                    } else {
                        excelTalkOrderInfo.setChannel("微信支付");
                    }
                    //主订单uid
                    if (StringUtils.isBlank(excelTalkOrderInfo.getNickname()) && storeOrder.getUid() != null) {
                        User user = userService.getById(storeOrder.getUid());
                        if (user != null) {
                            excelTalkOrderInfo.setNickname(user.getNickname());
                            excelTalkOrderInfo.setPhone(user.getPhone());
                            excelTalkOrderInfo.setEmail(user.getEmail());
                        }
                    }
                } else {
//                    throw new VcaException("不存在该主订单：" + storeOrderInfo.getOrderNo());
                }
                //核销状态
                if (storeOrder != null) {
                    if (storeOrder.getPaid()) {
                        if (storeOrderInfo.getWriteOffClerkId() == null) {
                            excelTalkOrderInfo.setWriteOffStatus("待核销");
                        } else {
                            excelTalkOrderInfo.setWriteOffStatus("已核销");
                        }
                    } else {
                        excelTalkOrderInfo.setWriteOffStatus("待支付");
                    }
                }
                //核销时间
                if (storeOrderInfo.getWriteOffTime() != null) {
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm");
                    excelTalkOrderInfo.setWriteOffTime(simpleDateFormat.format(storeOrderInfo.getWriteOffTime()));
                }
                list.add(excelTalkOrderInfo);
            }
        }
        //构建execl格式
        String fileName = "talk" + System.currentTimeMillis() + ".xlsx";
        XSSFWorkbook workbook = ExportUtil.exportExcelTalkSchedulingById(header, header2, status, excelTalkSchedulingById);
        // 文件名部分
        ExportUtil.setUpload(vcaConfig.getImagePath(), Constants.UPLOAD_MODEL_PATH_EXCEL, Constants.UPLOAD_TYPE_FILE);

        String newFileName = UploadUtil.getWebPath() + fileName;
        String filePath = UploadUtil.getServerPath();
        // 判断是否存在当前目录，不存在则创建
        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdirs();
        }
        fileName = filePath.concat(fileName);
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(fileName);
            workbook.write(fileOutputStream);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            fileOutputStream.flush();
            fileOutputStream.close();
        }
        return vcaConfig.getServerName() + vcaConfig.getActive() + vcaConfig.getImagePath() + newFileName;
    }

    @Override
    public String exportExcelExhibitionSchedulingById(String exhibitionSchedulingId) throws IOException {
        /**
         * 查找排期数据
         */
        ExhibitionScheduling exhibitionScheduling = exhibitionSchedulingService.getById(exhibitionSchedulingId);
        if (exhibitionScheduling == null) {
            throw new VcaException("不存在这个展览排期");
        }
        Exhibition exhibition = exhibitionService.getById(exhibitionScheduling.getExhibitionId());
        if (exhibition == null) {
            throw new VcaException("不存在这个展览");
        }
        String name = exhibition.getName();
        //标题
        String[] header = {"展览名称", "日期", "时间", "展览状态", "限量", "预约人数", "已核销人数（出席人数）"};
        //标题数据
        ExcelExSchedulingById excelExSchedulingById = new ExcelExSchedulingById();
        BeanUtils.copyProperties(exhibitionScheduling, excelExSchedulingById);
        boolean status = checkExcelStatus(exhibitionScheduling.getSchedulingDate(), exhibitionScheduling.getSchedulingStartTime(), exhibitionScheduling.getSchedulingEndTime());
        excelExSchedulingById.setStatus(status ? "已结束" : "未开始");
        excelExSchedulingById.setName(exhibition.getName());
        /**
         * 查找核销或待核销的数据
         */
        String[] header2 = {"学员序号", "预约讲座人员", "预约讲座手机号", "用户昵称", "绑定电话", "绑定邮箱", "订单号", "支付方式", "核销情况", "核销时间"};
        List<ExcelExOrderInfo> list = new ArrayList();
        excelExSchedulingById.setList(list);
        LambdaQueryWrapper<StoreOrderInfo> storeOrderInfoLambdaQueryWrapper = new LambdaQueryWrapper<>();
        storeOrderInfoLambdaQueryWrapper.eq(StoreOrderInfo::getSchedulingId, exhibitionSchedulingId);
        //已核销
        LambdaQueryWrapper<StoreOrder> storeOrderLambdaQueryWrapper = new LambdaQueryWrapper<>();
        storeOrderLambdaQueryWrapper.and(wrapper -> wrapper.in(StoreOrder::getStatus, Arrays.asList(2, 3)).in(StoreOrder::getType, Arrays.asList(0, 1, 2, 3)).or(wrapper1 -> wrapper1.eq(StoreOrder::getPaid, false).eq(StoreOrder::getStatus, 0)));
        storeOrderLambdaQueryWrapper.eq(StoreOrder::getIsDel, false);
        List<StoreOrder> storeOrderList = orderService.list(storeOrderLambdaQueryWrapper);
        //已核销主订单
        List<String> storeOrderNoList = null;
        if (CollectionUtil.isNotEmpty(storeOrderList)) {
            storeOrderNoList = storeOrderList.stream().map(StoreOrder::getOrderId).collect(Collectors.toList());
        }
        //待核销
//        storeOrderInfoLambdaQueryWrapper.eq(StoreOrderInfo::getStatus, 1);
//        storeOrderInfoLambdaQueryWrapper.eq(StoreOrderInfo::getRefundStatus, 0);
        final List<String> finalStoreOrderNoList = storeOrderNoList;
        if (CollectionUtil.isNotEmpty(finalStoreOrderNoList)) {
            storeOrderInfoLambdaQueryWrapper.and(wrapper -> wrapper.eq(StoreOrderInfo::getStatus, 1).eq(StoreOrderInfo::getRefundStatus, 0).or().in(CollectionUtil.isNotEmpty(finalStoreOrderNoList), StoreOrderInfo::getOrderNo, finalStoreOrderNoList));
        } else {
            storeOrderInfoLambdaQueryWrapper.eq(StoreOrderInfo::getStatus, 1).eq(StoreOrderInfo::getRefundStatus, 0);
        }
        List<StoreOrderInfo> storeOrderInfos = orderInfoService.list(storeOrderInfoLambdaQueryWrapper);
        if (CollectionUtil.isNotEmpty(storeOrderInfos)) {
            for (int i = 0; i < storeOrderInfos.size(); i++) {
                StoreOrderInfo storeOrderInfo = storeOrderInfos.get(i);
                ExcelExOrderInfo excelExOrderInfo = new ExcelExOrderInfo();
                BeanUtils.copyProperties(storeOrderInfo, excelExOrderInfo);
                //购买人
                User buyer = userService.getById(storeOrderInfo.getBuyer());
                if (buyer != null) {
                    excelExOrderInfo.setNickname(buyer.getNickname());
                    excelExOrderInfo.setPhone(buyer.getPhone());
                    excelExOrderInfo.setEmail(buyer.getEmail());
                } else {
//                    throw new VcaException("不存在该购买人：" + storeOrderInfo.getBuyer());
                }
                StoreOrder storeOrder = null;
                if (StringUtils.isBlank(excelExOrderInfo.getNickname())) {
                    //主订单
                    storeOrder = orderService.getByOrderId(storeOrderInfo.getOrderNo());
                    if (storeOrder != null) {
                        if (storeOrder.getUid() != null) {
                            User user = userService.getById(storeOrder.getUid());
                            if (user != null) {
                                excelExOrderInfo.setNickname(user.getNickname());
                                excelExOrderInfo.setPhone(user.getPhone());
                                excelExOrderInfo.setEmail(user.getEmail());
                            }
                        }
                    }
                }
                //主订单
                storeOrder = orderService.getByOrderId(storeOrderInfo.getOrderNo());
                if (storeOrder != null) {
                    //支付渠道
                    if (storeOrder.getUserType() == 1) {
                        excelExOrderInfo.setChannel("后台赠送");
                    } else {
                        excelExOrderInfo.setChannel("微信支付");
                    }
                } else {
//                    throw new VcaException("不存在该主订单："+storeOrderInfo.getOrderNo());
                }
                //核销状态
                if (storeOrder != null) {
                    if (storeOrder.getPaid()) {
                        if (storeOrderInfo.getWriteOffClerkId() == null) {
                            excelExOrderInfo.setWriteOffStatus("待核销");
                        } else {
                            excelExOrderInfo.setWriteOffStatus("已核销");
                        }
                    } else {
                        excelExOrderInfo.setWriteOffStatus("待支付");
                    }
                }
                //核销时间
                if (storeOrderInfo.getWriteOffTime() != null) {
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm");
                    excelExOrderInfo.setWriteOffTime(simpleDateFormat.format(storeOrderInfo.getWriteOffTime()));
                }
                list.add(excelExOrderInfo);
            }
        }
        //构建execl格式
        String fileName = "exhibition" + System.currentTimeMillis() + ".xlsx";
        XSSFWorkbook workbook = ExportUtil.exportExcelExSchedulingById(header, header2, status, excelExSchedulingById);
        // 文件名部分
        ExportUtil.setUpload(vcaConfig.getImagePath(), Constants.UPLOAD_MODEL_PATH_EXCEL, Constants.UPLOAD_TYPE_FILE);

        String newFileName = UploadUtil.getWebPath() + fileName;
        String filePath = UploadUtil.getServerPath();
        // 判断是否存在当前目录，不存在则创建
        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdirs();
        }
        fileName = filePath.concat(fileName);
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(fileName);
            workbook.write(fileOutputStream);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            fileOutputStream.flush();
            fileOutputStream.close();
        }
        return vcaConfig.getServerName() + vcaConfig.getActive() + vcaConfig.getImagePath() + newFileName;
    }

    /**
     * 检测开始还是结束
     *
     * @param date
     * @param startTime
     * @param endTime
     * @return
     */
    @Override
    public boolean checkExcelStatus(String date, String startTime, String endTime) {
//        LocalDateTime now = LocalDateTime.now();
//
//        String[] dateSplit = date.split("-");
//        Integer year = Integer.valueOf(dateSplit[0]);
//        Integer month = Integer.valueOf(dateSplit[1]);
//        Integer day = Integer.valueOf(dateSplit[2]);
//        String[] startSplit = startTime.split(":");
//        Integer startHour = Integer.valueOf(startSplit[0]);
//        Integer startMin = Integer.valueOf(startSplit[1]);
//        String[] endSplit = endTime.split(":");
//        Integer endHour = Integer.valueOf(endSplit[0]);
//        Integer endtMin = Integer.valueOf(endSplit[1]);
//
//        LocalDateTime start = LocalDateTime.of(year, month, day, startHour, startMin);
//        LocalDateTime end = LocalDateTime.of(year, month, day, endHour, endtMin);

        Date now = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        String startDateTime = date + " " + startTime;
        String endDateTime = date + " " + endTime;
        Date startDate = null;
        Date endDate = null;
        try {
            startDate = sdf.parse(startDateTime);
            endDate = sdf.parse(endDateTime);
        } catch (ParseException e) {
            throw new VcaException("时间格式错误");
        }

        if (now.before(startDate)) {
            return false;
        } else if (now.after(endDate)) {
            return true;
        } else {
            return false;
        }
    }

    @Override
    public String testDetailClient(String dateLimit, String fileName, HttpServletResponse response) throws IOException {
        dateLimitUtilVo dateLimitUtilVo = DateUtil.getDateLimit(dateLimit);
        String startTime = dateLimitUtilVo.getStartTime().substring(0, 10);
        String endTime = DateUtil.addDateMinut(dateLimitUtilVo.getEndTime().substring(0, 10) + " 00:00:00", 24).substring(0, 10);
        dateLimitUtilVo.getEndTime().substring(0, 10);
        List<DetailClientVo> vos = orderService.testDetailClient(new StoreDateRangeSqlPram(startTime, endTime));

        //头部信息
        List<String> headers = new ArrayList<>();
        headers.add("No.");
        headers.add("Client code 客人编号");
        headers.add("Client name 客人姓名");
        headers.add("Product segment 商品分类");
        headers.add("Product name 商品名称");
        headers.add("Receipt number 交易单号");
        headers.add("Merchant order number 商户单号");
        headers.add("Gift card number 礼品卡卡号");
        headers.add("Gift status 赠送状态");
        headers.add("Usage status 使用状态");
        headers.add("Usage time  兑换时间");
        headers.add("Course name 课程名称");
        headers.add("Course period 课程期数");
        headers.add("Payment date 付款日");
        headers.add("Payment total amount 付款总金额");
        headers.add("Payment method 支付方式");
        headers.add("Redeemed date 核销时间");
        headers.add("Redeemed amount 已使用金额");
        headers.add("Remain balance 剩余金额");
        headers.add("Fapiao issue date 发票开具日期");
        headers.add("Fapiao type 发票类型");
        headers.add("Fapiao amount 发票金额");
        headers.add("Transaction type 交易类型");
        headers.add("Refund amount 退款金额");
        headers.add("Refund price diference 退款差价");
        headers.add("Refund date 退款日期");
        headers.add("Fapiao adjustment amount 发票调整金额");
        headers.add("Fapiao adjustment date 发票调整日期");



        List<List<Object>> result = new ArrayList<>();//最终数据源
        AtomicInteger i  = new AtomicInteger(1);
        vos.forEach(detailClientVo -> {
            List<Object> elementList = new ArrayList<>();
            elementList.add(String.valueOf(i));
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getUid()) ? detailClientVo.getUid().toString() : "");
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getNickName()) ? detailClientVo.getNickName() : "");
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getType()) ? VcaUtil.orderTypeToName(detailClientVo.getType()) : "");
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getName())?detailClientVo.getName():"");
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getOrderNo()) ? detailClientVo.getOrderNo() : "");
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getUmsMerOrderId()) ? detailClientVo.getUmsMerOrderId() : "");
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getCardNo()) ? detailClientVo.getCardNo() : (ObjectUtil.isNotEmpty(detailClientVo.getCouponNo()) ? detailClientVo.getCouponNo() : ""));
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getCardUid()) ? detailClientVo.getUid().equals(detailClientVo.getCardUid()) ? "未赠送" : "已赠送" : "");

            Boolean use = false;
            if (ObjectUtil.isNotEmpty(detailClientVo.getCardStatus())) {
                if (ObjectUtil.isNotEmpty(detailClientVo.getPeriods()) && detailClientVo.getPeriods() > 0) {
                    use = true;
                    elementList.add("已使用");
                } else {
                    switch (detailClientVo.getCardStatus()) {
                        case 0:
                            elementList.add("未使用");
                            break;
                        case 1:
                            elementList.add("已使用");
                            use = true;
                            break;
                        case 2:
                            elementList.add("已失效");
                            break;
                        default:
                            elementList.add("");
                            break;
                    }
                }
            } else {
                elementList.add("");
            }
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getUseTime()) ? DateUtil.dateToStr(detailClientVo.getUseTime(), Constants.DATE_FORMAT) : "");//兑换时间
            //课程名称和期数
            if (ObjectUtil.isNotEmpty(detailClientVo.getCouponName())) {
                elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getCouponName()) ? VcaUtil.stringToArrayStr(detailClientVo.getCouponName()).get(0) : "");
                elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getPeriods()) ? "1" : "");
            } else if (!detailClientVo.getType().equals(4)) {
                elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getName()) ? VcaUtil.stringToArrayStr(detailClientVo.getName()).get(0) : "");
                elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getPeriods()) ? "1" : "");
            } else {
                elementList.add("");
                elementList.add("");
            }
            //支付时间
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getPayTime()) ? DateUtil.dateToStr(detailClientVo.getPayTime(), Constants.DATE_FORMAT) : "");
            //付款总金额
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getSchedulingPrice()) ? new BigDecimal(detailClientVo.getSchedulingPrice()).divide(BigDecimal.valueOf(detailClientVo.getPayNum())).doubleValue() : "");
            elementList.add(detailClientVo.getUserType().equals(1) ? "Presenter" : "Online");

            if(detailClientVo.getType().equals(4)){
                if(detailClientVo.getCardNo()!=null){
                    QueryWrapper<UserCard> queryWrapper = new QueryWrapper<>();
                    queryWrapper.eq("card_no",detailClientVo.getCardNo());
                    UserCard userCard = userCardService.getOne(queryWrapper);
                    if(userCard!=null){
                        QueryWrapper<StoreOrder> queryWrapper1 = new QueryWrapper<>();
                        queryWrapper1.eq("coupon_id",userCard.getId());
                        StoreOrder storeOrder = orderService.getOne(queryWrapper1);
                        if(storeOrder!=null){
                            QueryWrapper<StoreOrderInfo> queryWrapper2 = new QueryWrapper<>();
                            queryWrapper2.eq("order_no",storeOrder.getOrderId());
                            StoreOrderInfo storeOrderInfo = orderInfoService.getOne(queryWrapper2);
                            elementList.add(ObjectUtil.isNotEmpty(storeOrderInfo.getWriteOffTime()) ? DateUtil.dateToStr(storeOrderInfo.getWriteOffTime(), Constants.DATE_FORMAT) : "");
                        }else {
                            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getWriteOffTime()) ? detailClientVo.getWriteOffTime() : "");
                        }
                    }
                }else {
                    elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getDeliveryTime()) ? DateUtil.dateToStr(detailClientVo.getDeliveryTime(), Constants.DATE_FORMAT) : "");
                }
            }else {
                elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getWriteOffTime()) ? detailClientVo.getWriteOffTime() : "");
            }


//            elementList.add("");

            Boolean status = false;

            if(detailClientVo.getType().equals(0)&&detailClientVo.getUmsMerOrderId()!=null){
                if(detailClientVo.getStatus().equals("2")||detailClientVo.getStatus().equals("3")){
                    status = true;
                    use = true;
                }
            }else if(detailClientVo.getType().equals(1)&&detailClientVo.getUmsMerOrderId()!=null){
                if(detailClientVo.getStatus().equals("2")||detailClientVo.getStatus().equals("3")){
                    status = true;
                    use = true;
                }
            }else {
                if(detailClientVo.getType().equals(4)&&detailClientVo.getCardNo()!=null){
                    QueryWrapper<UserCard> queryWrapper = new QueryWrapper<>();
                    queryWrapper.eq("card_no",detailClientVo.getCardNo());
                    List<UserCard> list = userCardService.list(queryWrapper);

                    if(list.size()>0){
                        Integer couponId= list.get(0).getId();
                        QueryWrapper<StoreOrder> queryWrapper1 = new QueryWrapper<>();
                        queryWrapper1.eq("coupon_id",couponId);
                        queryWrapper1.eq("refund_status","0");
                        List<StoreOrder> list1 = orderService.list(queryWrapper1);
                        if(list1.size()>0&&list1.get(0).getStatus().equals(2)){
                            status = true;
                        }
                    }
                }else if(detailClientVo.getType().equals(4)&&detailClientVo.getMasterWaybillNo()!=null){
                    status = true;
                    use = true;
                }
            }

            if(detailClientVo.getRefundPrice()!=null&&detailClientVo.getRefundPrice().compareTo(BigDecimal.ZERO) > 0){
                elementList.add("");
                elementList.add(BigDecimal.ZERO.doubleValue());
            }else {
                if(use&&status){
                    elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getSchedulingPrice()) ? new BigDecimal(detailClientVo.getSchedulingPrice()).divide(BigDecimal.valueOf(detailClientVo.getPayNum())).doubleValue():"");
                    elementList.add("");
                }else {
                    elementList.add("");
                    elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getSchedulingPrice()) ? new BigDecimal(detailClientVo.getSchedulingPrice()).divide(BigDecimal.valueOf(detailClientVo.getPayNum())).doubleValue():"");
                }
            }
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getInvoiceTime()) ? detailClientVo.getInvoiceTime() : "");
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getInvoiceTime()) ? "电子增值税普通发票" : "");
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getInvoicePrice()) ? detailClientVo.getInvoicePrice().divide(BigDecimal.valueOf(detailClientVo.getPayNum())) : "");
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getRefundPrice()) ? detailClientVo.getRefundPrice().compareTo(BigDecimal.ZERO) > 0 ? "退款" : "无" : "无");
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getRefundPrice()) ? detailClientVo.getRefundPrice().compareTo(BigDecimal.ZERO) > 0 ? detailClientVo.getRefundPrice().doubleValue() : "" : "");
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getRefundPrice()) ? detailClientVo.getRefundPrice().compareTo(BigDecimal.ZERO) > 0 ? new BigDecimal(detailClientVo.getSchedulingPrice()).divide(BigDecimal.valueOf(detailClientVo.getPayNum())).subtract(detailClientVo.getRefundPrice()).doubleValue() : "" : "");
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getRefundTime()) ? DateUtil.dateToStr(detailClientVo.getRefundTime(), Constants.DATE_FORMAT) : "");
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getCreditNotePrice()) ? detailClientVo.getCreditNotePrice().toString() : "");
            elementList.add(ObjectUtil.isNotEmpty(detailClientVo.getCreditNoteTime()) ? detailClientVo.getCreditNoteTime() : "");

            i.getAndIncrement();

            result.add(elementList);
        });
        ExportUtil.setUpload(vcaConfig.getImagePath(), Constants.UPLOAD_MODEL_PATH_EXCEL, Constants.UPLOAD_TYPE_FILE);

        String newFileName = UploadUtil.getWebPath() + fileName;
        ExcelWriter writer = ExcelUtil.getWriter(vcaConfig.getImagePath() +newFileName);

        String filePath = UploadUtil.getServerPath();

//        File file = new File(filePath);
//        if (!file.exists()) {
//            file.mkdirs();
//        }
//        FileUtil.mkParentDirs(filePath);

        //写出表头
        writer.writeHeadRow(headers);
        writer.write(result);

        Font headFont = writer.createFont();
        headFont.setColor(IndexedColors.BLACK.index);
        for (int j = 0; j < headers.size(); j++) {
            if(j<=15){
                // 设置第一列表头样式
                XSSFCellStyle style1 = (XSSFCellStyle) writer.createCellStyle();
                style1.setAlignment(HorizontalAlignment.CENTER);
                style1.setBorderTop(BorderStyle.THIN); // 上边框
                style1.setBorderLeft(BorderStyle.THIN); // 左边框
                style1.setVerticalAlignment(VerticalAlignment.CENTER);
                style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(215, 223, 241), new DefaultIndexedColorMap()));
                style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                style1.setFont(headFont);
                writer.getCell(j, 0).setCellStyle(style1);
            }else {
                // 设置第二列表头样式
                XSSFCellStyle style2 = (XSSFCellStyle) writer.createCellStyle();
                style2.setAlignment(HorizontalAlignment.CENTER);
                style2.setBorderTop(BorderStyle.THIN); // 上边框
                style2.setBorderLeft(BorderStyle.THIN); // 左边框
                style2.setVerticalAlignment(VerticalAlignment.CENTER);
                style2.setFillForegroundColor(new XSSFColor(new java.awt.Color(253, 239, 202), new DefaultIndexedColorMap()));
                style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                style2.setFont(headFont);
                writer.getCell(j, 0).setCellStyle(style2);
            }
        }
        for (int j = 0; j < headers.size(); j++) {
            writer.setColumnWidth(j, 30);

        }
        for (int k = 0; k <= result.size(); k++) {
            writer.setRowHeight(k,50);
        }


        // 关闭writer，释放内存

        writer.close();

//        os.close();
        return vcaConfig.getServerName() + vcaConfig.getActive() + vcaConfig.getImagePath() +newFileName;
    }

    @Override
    public String testDetailClient1(String dateLimit, String fileName, HttpServletResponse response){
        List<String> headers = new ArrayList<>();
        headers.add("No.");
        headers.add("Client code 客人编号");
        headers.add("Client name 客人姓名");
        headers.add("Product segment 商品分类");
        headers.add("Product name 商品名称");
        List<Object> row1 = CollUtil.newArrayList("L'ÉCOLE青少年工坊L'ÉCOLE青少年工坊", "bb", "cc", 1);
        List<Object> row2 = CollUtil.newArrayList("aa1", "bb1", "cc1", 2);
        List<Object> row3 = CollUtil.newArrayList("aa2", "bb2", "cc2", 3);
        List<Object> row4 = CollUtil.newArrayList("aa3", "bb3", "cc3", 4);
        List<Object> row5 = CollUtil.newArrayList("aa4", "bb4", "cc4", 5);

        List<List<Object>> rows = CollUtil.newArrayList(row1, row2, row3, row4, row5);

        ExportUtil.setUpload(vcaConfig.getImagePath(), Constants.UPLOAD_MODEL_PATH_EXCEL, Constants.UPLOAD_TYPE_FILE);
        String newFileName = UploadUtil.getWebPath() + fileName;

        ExcelWriter writer = ExcelUtil.getWriter(vcaConfig.getImagePath() +newFileName);
        // 获取自定义样式
        StyleSet style = writer.getStyleSet();

        // 创建字体
        Font headFont = writer.createFont();
        headFont.setBold(true); // 加粗
        headFont.setColor(IndexedColors.WHITE.index); // 调整字体颜色

        // 创建表头背景色样式
        CellStyle headCellStyle = style.getHeadCellStyle();
        headCellStyle.setFillForegroundColor(IndexedColors.BLUE.index); // 设置背景蓝色
        headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置填充模式
        headCellStyle.setFont(headFont); // 设置字体

        writer.setRowStyle(0,headCellStyle);

        writer.writeHeadRow(headers);
        writer.write(rows);
        writer.setDefaultRowHeight(50);
        for (int j = 0; j < headers.size(); j++) {
            writer.setColumnWidth(j, 30);
        }
        writer.setDefaultRowHeight(50);


        String filePath = UploadUtil.getServerPath();

        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdirs();
        }
//        fileName = filePath.concat(fileName);
//        OutputStream os = new FileOutputStream(fileName);

//        ServletOutputStream out=response.getOutputStream();
//        writer.flush(os, true);
        // 关闭writer，释放内存
        writer.close();
//        os.close();
        return vcaConfig.getServerName() + vcaConfig.getActive() + vcaConfig.getImagePath() +newFileName;
    }


}
